import os

import pymysql

# 数据库连接配置
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='123456',
    database='hqyj',
    port=3306,
    charset='utf8'
)

cursor = connection.cursor()

# 创建图书表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS file_books (
        id INT PRIMARY KEY AUTO_INCREMENT,
        book_name VARCHAR(255) NOT NULL,
        content LONGTEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')

# 指定图书目录路径
book_directory = './books'

# 遍历目录中的所有.txt文件
if os.path.exists(book_directory):
    for filename in os.listdir(book_directory):
        if filename.endswith('.txt'):
            file_path = os.path.join(book_directory, filename)

            # 获取图书名（去掉.txt扩展名）
            book_name = os.path.splitext(filename)[0]

            # 读取文件内容
            try:
                with open(file_path, 'r', encoding='gbk', ) as file:
                    content = file.read()

                # 插入数据库
                sql = "INSERT INTO file_books (book_name, content) VALUES (%s, %s)"
                cursor.execute(sql, (book_name, content))
                print(f"已存储图书: {book_name}")

            except Exception as e:
                print(f"读取文件 {filename} 时出错: {e}")
else:
    print(f"目录 {book_directory} 不存在")

# 提交事务并关闭连接
connection.commit()
cursor.close()
connection.close()


print("所有图书已成功存储到数据库")
